package main;

import main.SQL.MsSQLConnect;
import main.Trees.SmetNode;

import java.sql.*;
import java.util.ArrayList;

/**
 * Created by s_shmakov on 25.04.14.
 */
public class SKSQLSmet {
    private SmetNode myroot;
    private Statement stat;
    private Connection connSql;
    private ArrayList<SmetNode> smc, smt, smo, sms, smr;

    public SmetNode open(int complex) {
        try {
            this.myroot = new SmetNode ("Все сметы");
            this.connSql = MsSQLConnect.ConnectDB("TST");

            getSmcList();
            getSmtList();
            getSmoList();
            getSmsList();
            getSmrList(complex);

            addHierarchy();

            this.connSql.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return myroot;
    }

    private void getSmcList () {
        this.smc = new ArrayList<> ();

        try {

            stat = connSql.createStatement();
            ResultSet rs = stat.executeQuery("SELECT complex_id, name FROM ks_Complex");

            while (rs.next()) {
                this.smc.add(new SmetNode(0, rs.getInt("complex_id"), rs.getString("name")));
            }

            rs.close();
            stat.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void getSmtList () {
        this.smt = new ArrayList<> ();

        try {
            stat = connSql.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM ks_Tituls");

            while (rs.next()) {
                this.smt.add(new SmetNode(rs.getInt("complex_id"), rs.getInt("titul_id"), rs.getString("name")));
            }

            rs.close();
            stat.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    private void getSmoList () {
        this.smo = new ArrayList<> ();

        try {
            stat = connSql.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM ks_Objects");

            while (rs.next()) {
                this.smo.add(new SmetNode(rs.getInt("titul_id"), rs.getInt("object_id"), rs.getString("name")));
            }

            rs.close();
            stat.close();
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }

    private void getSmsList () {
        this.sms = new ArrayList<> ();
        try {
            stat = connSql.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM ks_Smeta WHERE smetIsSSR = 0");
            while (rs.next()) {
                this.sms.add(new SmetNode(rs.getInt("object_id"), rs.getInt("smet_id"), "Смета " + rs.getString("smetNumber") + " - " + rs.getString("smetName")));
            }
            rs.close();
            stat.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void getSmrList (int complex) throws SQLException {
        CallableStatement stmt = connSql.prepareCall("{call getSmr(?)}");
        stmt.setInt(1, complex);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            this.smr.add(new SmetNode (rs.getString(2), rs.getInt(1), rs.getString(3), rs.getString(4), rs.getDouble(5), rs.getDouble(6), rs.getDouble(7), rs.getDouble(8), true, rs.getInt(9), "уйди а" ) );
        }

        rs.close();
        stat.close();

    }

    private void addHierarchy () {
        for (SmetNode curSmc : smc) {
            this.myroot.add(curSmc);
            addSmtChildren(curSmc);
        }
    }

    private void addSmtChildren (SmetNode parent) {
        for (SmetNode node : smt) {
            if (node.getParentId() != parent.getId()) continue;
            parent.add(node);
            addSmoChildren(node);
        }
    }

    private void addSmoChildren (SmetNode parent) {
        for (SmetNode node : smo) {
            if (node.getParentId() != parent.getId()) continue;
            parent.add(node);
            addSmsChildren(node);
        }
    }

    private void addSmsChildren (SmetNode parent) {
        for (SmetNode node : sms) {
            if (node.getParentId() != parent.getId()) continue;
            node.isString = true;
            node.setAllowChildren(false);
            parent.add(node);
        }
    }
}
